package com.metagurukul.informer.dao;

import com.metagurukul.informer.utility.ConnectionFactory;
import com.metagurukul.informer.valueObjects.*;
import java.sql.*;
import java.util.*;

/**
 * Class QuestionsDAO for interaction with the questions table of the database
 * 
 * @author
 * 
 */
public class QuestionsDAO {

	/**
	 * ...Method to extract a single row of question table using the question id
	 * 
	 * @param id
	 * @return question object
	 */
	public Questions findById(int id) throws SQLException {
		Questions question = null;
		Connection con = ConnectionFactory.getInstance();
		/*
		 * SQL query for extracting the question
		 */
		String sql = "SELECT * FROM questions WHERE id=" + id;
		Statement stm = con.createStatement();
		ResultSet rs = stm.executeQuery(sql);
		if (rs.next()) {
			// Creating the question object
			question = new Questions(rs.getInt("id"), rs.getString("title"),
					rs.getString("description"), rs.getBoolean("isclosed"),
					rs.getString("user_id"), rs.getDate("posted"));
		} else {
			question = null;
		}

		if(stm!=null){
	   		stm.close();
		}
	   	if(rs!=null) {
	   		rs.close();
	   	}
	   	
		if(con!=null){
			con.close();
		}
		
		return question;
	}

	/**
	 * ...Method For inserting a question in the questions table
	 * 
	 * @param question
	 * @return id of new inserted question
	 */
	public int insert(Questions question) throws SQLException {
		int question_id = 0;
		Connection con = ConnectionFactory.getInstance();
		/*
		 * query for insertion in the table
		 */
		String sql = "insert into questions(title,description,isclosed,posted,user_id) values(?,?,?,?,?)";
		PreparedStatement ps = null;
		ps = con.prepareStatement(sql);
		ps.setString(1, question.getTitle());
		ps.setString(2, question.getDescription());
		ps.setBoolean(3, question.isClosed());
		ps.setDate(4, question.getPosted());
		ps.setString(5, question.getUser_id());
		ps.executeUpdate();
		/*
		 * SQL query for extracting new inserted question_id
		 */
		sql = "SELECT id FROM questions order by id desc limit 1";
		Statement stm = con.createStatement();
		ResultSet rs = stm.executeQuery(sql);
		rs.next();
		question_id = rs.getInt(1);
		
		
		if(stm!=null){
	   		stm.close();
		}
		if(ps!=null){
			ps.close();
		}
		
	   	if(rs!=null) {
	   		rs.close();
	   	}
	   	
		if(con!=null){
			con.close();
		}
		
		
		return question_id;

	}

	/**
	 * ...Method to get all questions from database.
	 * 
	 * @return list of all questions
	 */
	public ArrayList<Questions> getAllQuestions() throws SQLException {
		
		Connection con = ConnectionFactory.getInstance();
		Questions question = null;
		ArrayList<Questions> listOfQuestions = new ArrayList<Questions>();

		/*
		 * SQL query for extracting questions according to the posted time
		 */
		String query = "select * from questions ORDER BY posted DESC;";
		Statement stm = con.createStatement();
		ResultSet rs = stm.executeQuery(query);

		/*
		 * To make a question object for each tuple of the output adding that
		 * object to the output question list
		 */
		while (rs.next()) {
			question = new Questions(rs.getInt("id"), rs.getString("title"),
					rs.getString("description"), rs.getBoolean("isclosed"),
					rs.getString("user_id"), rs.getDate("posted"));
			listOfQuestions.add(question);
		}
		
		if(stm!=null){
	   		stm.close();
		}
	   	if(rs!=null) {
	   		rs.close();
	   	}
	   	
		if(con!=null){
			con.close();
		}
		
		return listOfQuestions;
	}

	/**
	 * ...Method to get all questions which are not answered yet.
	 * 
	 * @return list of questions
	 */
	public List<Questions> getUnanswered() throws SQLException {
		
		Connection con = ConnectionFactory.getInstance();
		Statement stmt = null;
		ResultSet rs = null;
		List<Questions> questions = new ArrayList<Questions>();

		/*
		 * SQL query for extracting unanswered questions
		 */
		String sql = "select * from questions q "
				+ "where NOT EXISTS (SELECT * from answers where question_id = q.id)";
		stmt = con.createStatement();
		rs = stmt.executeQuery(sql);

		/*
		 * To make a question object for each tuple of the output adding that
		 * object to the output question list
		 */
		while (rs.next()) {
			questions.add(new Questions(rs.getInt(1), rs.getString(2), rs
					.getString(3), rs.getBoolean(6), rs.getString(4), rs
					.getDate(5)));
		}

		if(stmt!=null){
	   		stmt.close();
		}
	   	if(rs!=null) {
	   		rs.close();
	   	}
	   	
		if(con!=null){
			con.close();
		}
		
		return questions;
	}

	/**
	 * ...Method to delete a question with given id
	 * 
	 * @param id
	 * @return true if deletion successful else false
	 */
	public boolean DeleteById(int id) throws SQLException {
		
		Connection con = ConnectionFactory.getInstance();
		Statement stmt = null;
		int result = 0;

		// SQL query for delete a question by id
		String sql = "Delete from questions where id=" + id;
		stmt = con.createStatement();
		result = stmt.executeUpdate(sql);

		if (result > 0) {
			return true;
		}
		
		
		if(stmt!=null){
	   		stmt.close();
		}
	   	
		if(con!=null){
			con.close();
		}
		return false;
	}
}